﻿using DocumentFormat.OpenXml.Lite.Core.Office;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Base;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.RunProperties;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.StyleElements;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Types;
using ESE = DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.StyleElements;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.WorksheetElements;

namespace Samples.Excel
{
    class Styles
    {
        public static void Create()
        {
            Workbook wb = DocumentManager.Instance.CreateSpreadsheet();
                        
            StyleSheet ss = wb.CreateStyleSheet();

            ss.Fonts = new Fonts();
            ss.Fonts.AddFont(new ESE.Font());
            ss.Fonts.FontList.Last().AddElement(new FontSize() { Value = 11 });
            ss.Fonts.FontList.Last().AddElement(new FontName() { Value = "Calibri" });

            //defaults
            ss.Fills = new Fills();
            ss.Fills.AddFill(new Fill()
            {
                PatternFill = new PatternFill() { Type = ST_PatternType.None }
            });
            ss.Fills.AddFill(new Fill()
            {
                PatternFill = new PatternFill() { Type = ST_PatternType.Gray125 }
            });
            //even row
            uint evenRowFill = ss.Fills.AddFill(new Fill()
            {
                PatternFill = new PatternFill()
                {
                    Type = ST_PatternType.Solid,
                    BackgroundColor = new ESE.Color() { RBG = "FFBDD6EE" },
                    ForegroundColor = new ESE.Color() { Theme = 4, Tint = 0.799981688894314420 }
                }
            });

            //defaults
            ss.Borders = new ESE.Borders();
            ss.Borders.AddBorder(new ESE.Border()
            {
                Left = new ESE.BorderElement(),
                Right = new ESE.BorderElement(),
                Top = new ESE.BorderElement(),
                Bottom = new ESE.BorderElement(),
                Diagonal = new ESE.BorderElement()
            });
            //even row border
            uint border = ss.Borders.AddBorder(new ESE.Border()
            {
                Left = new ESE.BorderElement(),
                Right = new ESE.BorderElement(),
                Top = new ESE.BorderElement()
                {
                    BorderStyle = ST_BorderStyle.Double,
                    Color = new ESE.Color() { RBG = "FF488CD0" }
                },
                Bottom = new ESE.BorderElement()
                {
                    BorderStyle = ST_BorderStyle.Thin,
                    Color = new ESE.Color() { RBG = "FF488CD0" }
                },
                Diagonal = new ESE.BorderElement()
            });

            //defaults
            ss.RecordFormats = new ESE.RecordFormats();
            ss.RecordFormats.AddElement(new ESE.FormattingElement()
            {
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 0
            });

            //defaults
            ss.CellFormats = new CellFormats();
            ss.CellFormats.AddElement(new ESE.FormattingElement()
            {
                NumberFormatId = 0,
                FillId = 0,
                FontId = 0,
                BorderId = 0,
                FormatId = 0
            });
            //even row
            uint evenRowFE = ss.CellFormats.AddElement(new ESE.FormattingElement()
            {
                NumberFormatId = 0,
                FillId = evenRowFill,
                FontId = 0,
                BorderId = border,
                FormatId = 0,
                ApplyAlignment = true,
                Alignment = new ESE.Alignment() { Horizontal = ST_HorizontalAlignment.Center }
            });

            Worksheet sheet1 = wb.CreateWorksheet("Sheet1", null);

            //create cells and apply style
            for (int i = 1; i < 5; ++i)
            {
                for (int j = 1; j < 5; ++j)
                {
                    Cell c = sheet1.CreateCell(i, j);
                    c.CellType = ST_CellType.String;
                    c.StyleIndex = (i % 2 == 0 ? "0" : "1");
                    c.AddSimpleText("Row: " + i.ToString() + "; Column: " + j.ToString());
                }
            }

            //make columns a bit wider
            ColumnInformation info = sheet1.AddColumnInformation();
            for (uint i = 1; i < 5; ++i)
            {
                info.Informations.Add(new Column()
                {
                    Minimum = i,
                    Maximum = i,
                    Width = 30
                });
            }

            wb.Save(@"samples\styles.xlsx");

            Workbook parsed = null;
            if (DocumentManager.Instance.OpenExcelDocument(@"samples\styles.xlsx", out parsed))
            {
                Console.WriteLine("Sample created and parsed succesfully!");
            }
            Console.ReadLine();
        }
    }
}
